﻿Imports VN.INS.DL.DH
Imports VN.INS.BL.BE

Public Class PacDA

    Public Function cargar_pac(ByVal PPER As String, ByVal PERIODO As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String

        Try

            lxc_SQL = "    SELECT PAC.PAC, PAC.PPER, DESCRIPCION_BIENES,VALOR_ESTIMADO, TIPO_PROCESO, MES_PREVISTO,DURACION,  PLAZO_EJECUCION, " + _
            " FUENTE_FINANCIAMIENTO, META_PRESUPUESTAL, LINEA_ACCION, ACTIVIDAD, NUM_REFERENCIA_PAC, RESPONSABLE_AREA, " + _
             " RESPONSABLE_PPER, SEG.PERIODO, SEG.SEMAFORO, SEG.SEG_PAC ,SEG.SEMAFORO , RESPONSABLE_PROCESO, N_PEDIDO_SIGA, N_HOJA_TRAMITE, PPER.ABREVIATURA, SEG.COMENTARIO, SEG.ACCION " + _
             " FROM PAC INNER  JOIN PPER ON PPER.PPER = PAC.PPER " + _
             " LEFT JOIN SEG_PERIODO_AVAN_PAC SEG ON PAC.PAC = SEG.PAC  and SEG.PERIODO='" + PERIODO + "'  " + _
             " WHERE PAC.PPER='" + PPER + "'"

            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt

        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function CargarFechaPAC(ByVal strAno As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT CODIGO, DESCRIPCION " + _
                    "  FROM GENERAL " + _
                    "  WHERE ID = 'FECHA_PAC' " + _
                    "  AND ESTADO = 'A' " + _
                    "  AND PARAMETRO = '" & strAno & "' " + _
                    "  ORDER BY TO_NUMBER(CODIGO)"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Function MostrarFechas(ByVal strSeg As String)
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT ID_FECHA, FECHA " + _
                    "  FROM PAC_FECHAS " + _
                    "  WHERE SEG_PAC = '" & strSeg & "' " + _
                    "  ORDER BY TO_NUMBER(ID_FECHA)"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function ListaPeriodoPAC(ByVal ano As String) As DataTable
        Dim objData As AdministradorOracle = New AdministradorOracle
        Dim objDt As DataTable
        Dim lxc_SQL As String
        Try
            lxc_SQL = "SELECT ESTADO,PERIODO, DESCRIPCION FROM PERIODO WHERE TIPO_PERIODO = 'AVANCE_PAC' AND ANHIO = '" & ano & "' " + _
                      " ORDER BY PERIODO DESC"
            objDt = objData.ExecuteSQL(lxc_SQL, "Conexion.Oracle")
            Return objDt
        Catch ex As Exception
            Throw ex
        End Try
    End Function

    Public Sub insert_avance_pac(ByVal id_seg As String, ByVal id_pac As String, ByVal stado As String, ByVal accion As String, ByVal comentario As String, ByVal periodo As String)
        Dim objData As AdministradorOracle = New AdministradorOracle

        Try

            objData.CreateInputOracle("SEGX", id_seg, 20)
            objData.CreateInputOracle("PACX", id_pac, 20)
            objData.CreateInputOracle("EST", stado, 20)
            objData.CreateInputOracle("AC", accion, 2000)
            objData.CreateInputOracle("COMENT", comentario, 1500)
            objData.CreateInputOracle("PRD", periodo, 20)


            objData.ExecuteOracle_NonQuery("INSERT_SEG_AVANCE_PAC", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub update_avance_pac(ByVal id_seg As String, ByVal stado As String, ByVal accion As String, ByVal comentario As String, ByVal periodo As String)
        Dim objData As AdministradorOracle = New AdministradorOracle

        Try

            objData.CreateInputOracle("SEGX", id_seg, 20)
            objData.CreateInputOracle("EST", stado, 20)
            objData.CreateInputOracle("AC", accion, 2000)
            objData.CreateInputOracle("COMENT", comentario, 1500)
            objData.CreateInputOracle("PRD", periodo, 20)


            objData.ExecuteOracle_NonQuery("UPDATE_SEG_AVANCE_PAC", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Sub InsertarFechas(ByVal id_seg As String, ByVal id_fec As String, ByVal strFecha As String)
        Dim objData As AdministradorOracle = New AdministradorOracle

        Try

            objData.CreateInputOracle("ID_SEG", CInt(id_seg), 20)
            objData.CreateInputOracle("ID_FEC", id_fec, 20)
            objData.CreateInputOracle("FEC", strFecha, 10)

            objData.ExecuteOracle_NonQuery("INSERTAR_FECHA_PAC", "Conexion.Oracle")
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
End Class
